package com.xinghou.platform.dao.main.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import com.xinghou.platform.dao.bases.JdbcTemplate;
import com.xinghou.platform.dao.bases.database.CustomerContextHolder;
import com.xinghou.platform.dao.main.PhotoDAO;
import com.xinghou.platform.dao.main.SharedCardDAO;
import com.xinghou.platform.model.main.SharedCard;
import com.xinghou.platform.server.mq.MqMessage;
/**
 * @author liuwei.wen
 *
 */
@Service
public class SharedCardDAOImpl  {/*
	@Resource
	private JdbcTemplate jdbcTemplate;
	
	@Resource
	private PhotoDAO photoDAO;
	
	@Resource
	private MqMessage mqMessage;
	
	@Override
	public SharedCard getSharedCardBycardId(Integer cardId)
			throws DataAccessException {
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_READ);
		String sql="SELECT * FROM shared_card where Card_id = ?";
		SharedCard sharedCard = jdbcTemplate.queryForObject(sql, new Object[] {cardId}, new SharedCardMapper());
		CustomerContextHolder.clearCustomerType();
		return sharedCard;
	}
	@Override
	public int updateSharedCard(SharedCard sharedCard)
			throws DataAccessException {
		StringBuffer strSQL = new StringBuffer(
				"UPDATE shared_card SET STATUS=?,Store_id=?, User_id=?, Content =?,Share_type =?,Praise_count=?,Browse_count=?,Create_time=?,"
				+ "discount=?,money=?,minsale=?,usearea=?,transaction=?,transactiontype=?,transactiontime=?,brokerage=?,last_pid=? Where Card_id =?"	
				);
		Object[] params = {sharedCard.getStatus(),sharedCard.getStoreId(),sharedCard.getUserId(),sharedCard.getContent(), sharedCard.getShareType(),sharedCard.getPraiseCount(), sharedCard.getBrowseCount(), sharedCard.getCreateTime(),
				sharedCard.getDiscount(),sharedCard.getMoney()==null?0:sharedCard.getMoney(),sharedCard.getMinsale(),sharedCard.getUsearea(),sharedCard.getTransaction(),sharedCard.getTransactiontype(),sharedCard.getTransactiontime(),sharedCard.getBrokerage(),sharedCard.getLast_pid(),sharedCard.getCardId()
		};
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_WRITE);
		int ret = jdbcTemplate.update(strSQL.toString(),params);
		CustomerContextHolder.clearCustomerType();
		
		
		if(ret>0){
			mqMessage.sendMessageCard(sharedCard.getCardId()+"",true);
		}
		return ret;
	}
	@Override
	public int saveSharedCard(SharedCard sharedCard) throws DataAccessException {
		StringBuffer strSQL = new StringBuffer(
				"INSERT INTO shared_card (Store_id,STATUS,User_id,Content,Share_type,Praise_count,Browse_count,Create_time,discount,money,minsale,usearea,transaction,transactiontype,transactiontime,brokerage,last_pid)VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
				);
		Object[] params = {sharedCard.getStoreId(),sharedCard.getStatus(),sharedCard.getUserId(),sharedCard.getContent(), sharedCard.getShareType(),sharedCard.getPraiseCount(),sharedCard.getBrowseCount(),sharedCard.getCreateTime(),
				sharedCard.getDiscount(),sharedCard.getMoney()==null?0:sharedCard.getMoney(),sharedCard.getMinsale(),sharedCard.getUsearea(),sharedCard.getTransaction(),sharedCard.getTransactiontype(),sharedCard.getTransactiontime(),sharedCard.getBrokerage(),sharedCard.getLast_pid()
		};
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_WRITE);
		int ret =jdbcTemplate.save(strSQL.toString(),params);
		CustomerContextHolder.clearCustomerType();
		if(ret>0){
			mqMessage.sendMessageCard(ret+"",false);
		}
		return ret;
	}
	@Override
	public List<SharedCard> getSharedCardList() throws DataAccessException {
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_READ);
		String sql="SELECT * FROM shared_card where STATUS=1";
		List<SharedCard> SharedCardList=jdbcTemplate.query(sql, new SharedCardMapper());
		return SharedCardList;
	}
	private static final class SharedCardMapper implements RowMapper<SharedCard> {
		public SharedCard mapRow(ResultSet rs, int rowNum) throws SQLException {
			SharedCard sharedCard = new SharedCard();
			sharedCard.setCardId(rs.getInt("Card_id"));
			sharedCard.setContent(rs.getString("Content"));
			//sharedCard.setCreateTime(rs.getTimestamp("Create_time"));
			sharedCard.setPraiseCount(rs.getInt("Praise_count"));
			sharedCard.setBrowseCount(rs.getInt("Browse_count"));
			sharedCard.setShareType(rs.getByte("Share_type"));
			sharedCard.setStoreId(rs.getInt("Store_id"));
			sharedCard.setUserId(rs.getInt("User_id"));
			sharedCard.setStatus(rs.getShort("STATUS"));
			sharedCard.setDiscount(rs.getString("discount"));
			sharedCard.setMoney(rs.getDouble("money"));
			sharedCard.setMinsale(rs.getDouble("minsale"));
			sharedCard.setUsearea(rs.getString("usearea"));	
			sharedCard.setTransaction(rs.getShort("transaction"));
			sharedCard.setTransactiontype(rs.getShort("transactiontype"));
			sharedCard.setTransactiontime(rs.getString("transactiontime"));
			sharedCard.setBrokerage(rs.getDouble("brokerage"));
			sharedCard.setLast_pid(rs.getInt("last_pid"));
			return sharedCard;
		}
	}
	
	private static final class SharedCardMapper1 implements RowMapper<SharedCard> {
		public SharedCard mapRow(ResultSet rs, int rowNum) throws SQLException {
			SharedCard sharedCard = new SharedCard();
			sharedCard.setCardId(rs.getInt("b.Card_id"));
			sharedCard.setContent(rs.getString("b.Content"));
			//sharedCard.setCreateTime(rs.getTimestamp("b.Create_time"));
			sharedCard.setPraiseCount(rs.getInt("b.Praise_count"));
			sharedCard.setBrowseCount(rs.getInt("b.Browse_count"));
			sharedCard.setShareType(rs.getByte("b.Share_type"));
			sharedCard.setStoreId(rs.getInt("b.Store_id"));
			sharedCard.setUserId(rs.getInt("b.User_id"));
			sharedCard.setStatus(rs.getShort("b.STATUS"));
			sharedCard.setDiscount(rs.getString("b.discount"));
			sharedCard.setMoney(rs.getDouble("b.money"));
			sharedCard.setMinsale(rs.getDouble("b.minsale"));
			sharedCard.setUsearea(rs.getString("b.usearea"));	
			sharedCard.setTransaction(rs.getShort("b.transaction"));
			sharedCard.setTransactiontype(rs.getShort("b.transactiontype"));
			sharedCard.setTransactiontime(rs.getString("b.transactiontime"));
			sharedCard.setBrokerage(rs.getDouble("b.brokerage"));
			sharedCard.setLast_pid(rs.getInt("b.last_pid"));
			return sharedCard;
		}
	}
	
	@Override
	public List<SharedCard> getSharedCard(Long num, int userid)throws DataAccessException {
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_READ);
		String sql="SELECT * FROM shared_card where STATUS=1 and User_id=? and Create_time<? ORDER BY Create_time DESC LIMIT 10";
		List<SharedCard> sharedCardList=jdbcTemplate.query(sql, new Object[] {userid,num}, new SharedCardMapper());
		return sharedCardList;
	}

	@Override
	public List<SharedCard> getSharedCardList(Timestamp num, String type,int userid,int ftype) throws DataAccessException {
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_READ);
		List<SharedCard> sharedCardList;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM shared_card where STATUS=1 and Create_time<? ");
		if(userid == -1){
			if(type.equals("3")){
				sql.append("ORDER BY Create_time DESC LIMIT 10");
				sharedCardList=jdbcTemplate.query(sql.toString(), new Object[] {num}, new SharedCardMapper());
			}else{
				sql.append("and Share_type = ? ORDER BY Create_time DESC LIMIT 10");
				sharedCardList=jdbcTemplate.query(sql.toString(), new Object[] {num,type}, new SharedCardMapper());
			}
		}else{
			if(ftype == 0)
				if(type.equals("3")){
					sql.append("and User_id = ? ORDER BY Create_time DESC LIMIT 10");
					sharedCardList=jdbcTemplate.query(sql.toString(), new Object[] {num,userid}, new SharedCardMapper());
				}else{
					sql.append("and User_id = ? and Share_type = ? ORDER BY Create_time DESC LIMIT 10");
					sharedCardList=jdbcTemplate.query(sql.toString(), new Object[] {num,userid,type}, new SharedCardMapper());
				}
			else{
				sql = new StringBuffer();
//				sharedCard.setDiscount(rs.getString("b.discount"));
//				sharedCard.setMoney(rs.getDouble("b.money"));
//				sharedCard.setMinsale(rs.getDouble("b.minsale"));
//				sharedCard.setUsearea(rs.getString("b.usearea"));	
//				sharedCard.setTransaction(rs.getShort("b.transaction"));
//				sharedCard.setTransactiontype(rs.getShort("b.transactiontype"));
//				sharedCard.setTransactiontime(rs.getString("b.transactiontime"));
//				sharedCard.setBrokerage(rs.getDouble("b.brokerage"));
				sql.append("SELECT b.STATUS,b.Card_id,b.User_id,b.Store_id,b.Content,b.Share_type,b.Praise_count,b.Browse_count,b.Create_time,b.discount,b.money,b.minsale,b.usearea,"
						+ "b.transaction,b.transactiontype,b.transactiontime,b.brokerage,b.last_pid FROM shared_card b,shared_card_praise a WHERE b.Card_id = a.Card_id and a.STATUS=1 and b.Create_time<? ");
				if(type.equals("3")){
					sql.append("and a.User_id = ? ORDER BY b.Create_time DESC LIMIT 10");
					sharedCardList=jdbcTemplate.query(sql.toString(), new Object[] {num,userid}, new SharedCardMapper1());
				}else{
					sql.append("and a.User_id = ? and b.Share_type = ? ORDER BY b.Create_time DESC LIMIT 10");
					sharedCardList=jdbcTemplate.query(sql.toString(), new Object[] {num,userid,type}, new SharedCardMapper1());
				}
			}
		}
		
		
		return sharedCardList;
	}

*//*	@Override
	public List<SharedCard> sharCardSameShop(Integer userid)
			throws DataAccessException {
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_READ);
		String sql="SELECT t.Card_id,t.User_id,t.Share_type,t.Store_id,t.Content,(select Sex from user where User_id = t.User_id) AS sex,"
		 		+ "(SELECT Nick_name FROM user_info WHERE Info_id=(select Info_id from user where User_id = t.User_id)) AS Nick_name,"
		 		+ "(SELECT Birthday FROM user_info WHERE Info_id=(select Info_id from user where User_id = t.User_id)) AS Birthday,"
		 		+"(SELECT NAME FROM photo WHERE User_id = t.User_id ORDER BY Id DESC LIMIT 0,1) AS NAME,"
		 		+"(SELECT Photo_uri FROM photo WHERE User_id = t.User_id ORDER BY Id DESC LIMIT 0,1) AS Photo_uri,"
		 		+"(SELECT COUNT(*) FROM shared_card_browse where Card_id = t.Card_id) AS ViewCount,"
		 		+ "t.Praise_count,Browse_count,t.Create_time FROM shared_card t,message m WHERE t.Status = 1 and t.User_id = m.Sender_id"
		 		+ " AND m.Receiver_id=? ORDER BY m.Create_time DESC";
		List<SharedCard> list = jdbcTemplate.query(sql, new Object[] {userid}, new SharedCardMapper());
		CustomerContextHolder.clearCustomerType();
		return list;
	}*//*
	

	*//**
	 * 查询用户分享卡和求卡信息列表
	 *//*
	@Override
	public List<SharedCard> getSharedCardlist(byte cardtype, int userid)
			throws DataAccessException {
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_READ);
		String sql="SELECT * FROM shared_card where STATUS=1 and Share_type=? and User_id=?";
		List<SharedCard> sharedCardList=jdbcTemplate.query(sql, new Object[] {cardtype,userid}, new SharedCardMapper());
		return sharedCardList;
	}

	*//**
	 * 根据门店查询该店的分享卡信息  time时间以月为单位
	 *//*
	@Override
	public List<SharedCard> getSharedCardbyshoplist(int cardtype, int shopid,int time)
			throws DataAccessException {
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_READ);
		String sql = "";
		List<SharedCard> sharedCardList = new ArrayList<SharedCard>();
		if(time>0){
			sql="SELECT * "
					+ " FROM shared_card where STATUS=1 and Share_type=? and Store_id=? "
					+ "and Create_time>=DATE_SUB(CURDATE(), INTERVAL ? MONTH) ORDER BY Create_time DESC LIMIT 0,5";
		    sharedCardList=jdbcTemplate.query(sql, new Object[] {cardtype,shopid,Math.abs(time)}, new SharedCardMapper());
		}else{
			sql="SELECT * "
					+ " FROM shared_card where STATUS=1 and Share_type=? "
					+ "and Create_time<DATE_SUB(CURDATE(), INTERVAL ? MONTH)";
			sharedCardList=jdbcTemplate.query(sql, new Object[] {cardtype,Math.abs(time)}, new SharedCardMapper());
		}
		return sharedCardList;
	}

	@Override
	public List<SharedCard> getSharedCardbyshoplist(int cardtype,
			List<Integer> shopidlist) throws DataAccessException {
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_READ);
		String shopidall = "";
		for (int i = 0; i < shopidlist.size(); i++) {
			if(i<shopidlist.size()-1)
				shopidall = shopidall + shopidlist.get(i)+",";
			else
				shopidall = shopidall + shopidlist.get(i);
			
		}
		String sql="SELECT * FROM shared_card where STATUS=1 and Share_type=? and Store_id in"
				+ "("+shopidall+")"
				+ " ORDER BY Create_time DESC LIMIT 0,5";
		List<SharedCard> sharedCardList=jdbcTemplate.query(sql, new Object[] {cardtype}, new SharedCardMapper());
		return sharedCardList;
	}
	
	*//**
	 * 根据门店id查询分享卡信息并根据时间倒序排序 listnum表示查询数量
	 *//*
	public List<SharedCard> getSharedCardbyshoplist(int cardtype, int shopid,int listnum,Long shardcardid)
			throws DataAccessException {
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_READ);
		String sql = "";
		List<SharedCard> sharedCardList = new ArrayList<SharedCard>();
		sql="SELECT * "
			+ " FROM shared_card where STATUS=1 and Share_type=? and Store_id=? "
			+ "and Create_time <? ORDER BY Create_time DESC LIMIT ?";
		sharedCardList=jdbcTemplate.query(sql, new Object[] {cardtype,shopid,shardcardid,listnum}, new SharedCardMapper());

		return sharedCardList;
	}
	
	public List<SharedCard> getSharedCardlist(int listnum,int shardcardid,String key)
			throws DataAccessException {
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_READ);
		String sql = "";
		List<SharedCard> sharedCardList = new ArrayList<SharedCard>();
		key = "%"+key+"%";
		sql="SELECT * "
			+ "FROM shared_card where STATUS=1 and Content like ? "
			+ "and Card_id <? ORDER BY Create_time DESC LIMIT ?";
		sharedCardList=jdbcTemplate.query(sql, new Object[] {key,shardcardid,listnum}, new SharedCardMapper());
		
		return sharedCardList;
	}
	
	
	*//**
	 * 根据日期和门店销售卡的数量排序
	 * @param storeids
	 * @param date
	 * @return
	 *//*
	public List<Integer> getStoreidListsortbysellcount(List<Integer> storeids,String date)throws DataAccessException{
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_READ);
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT Store_id FROM shared_card WHERE DATE_FORMAT(Create_time, '%Y-%m-%d') = ? AND Store_id IN (");
		
		for (int i = 0; i < storeids.size(); i++) {
			sql.append(storeids.get(i));
			if(i!=(storeids.size()-1))
				sql.append(",");
		}
			
		sql.append(") GROUP BY Store_id ORDER BY Create_time DESC");
		
		return jdbcTemplate.query(sql.toString(), new Object[] {date}, new IntegerMapper());
	}
	
	*//**
	 * 根据门店分享卡的最新时间倒序排序每次查询20条
	 * @return
	 *//*
	public List<Integer> getStoreidListsortbysellcount(int skip)throws DataAccessException{
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_READ);
		StringBuffer sql = new StringBuffer();
		//sql.append("SELECT Store_id FROM shared_card");
		//sql.append(" GROUP BY Store_id ORDER BY Create_time DESC LIMIT ?,20");
		sql.append("SELECT Store_id FROM store where Type_id is not null and Telephone IS not NULL");
		sql.append(" ORDER BY Sale_count DESC LIMIT ?,20");

		return jdbcTemplate.query(sql.toString(), new Object[] {skip}, new IntegerMapper());
	}
	
	private static final class IntegerMapper implements RowMapper<Integer> {
		public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
			return rs.getInt(1);
		}
	}

	@Override
	public SharedCard getMinDiscount(int shopid) throws DataAccessException {
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_READ);
		String sql="SELECT * FROM shared_card where Store_id = ? ORDER BY discount LIMIT 1";
		SharedCard sharedCard = jdbcTemplate.queryForObject(sql, new Object[] {shopid}, new SharedCardMapper());
		CustomerContextHolder.clearCustomerType();
		return sharedCard;
	}
	@Override
	public SharedCard getSharedCardByDiscount(int shopid, Double discount)
			throws DataAccessException {
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_READ);
		String sql="SELECT * FROM shared_card where Store_id = ? AND discount=? LIMIT 1";
		SharedCard sharedCard = jdbcTemplate.queryForObject(sql, new Object[] {shopid,discount}, new SharedCardMapper());
		CustomerContextHolder.clearCustomerType();
		return sharedCard;
	}
	
	public List<SharedCard> getSharedCardById(int cardid, int pageSize) throws DataAccessException {
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_READ);
		String sql = "SELECT STATUS,Card_id,User_id,Store_id,Content,Share_type,Praise_count,Browse_count,Create_time FROM shared_card where share_type = 1 ORDER BY Card_id limit ?,?";
		System.out.println(sql+cardid+"00"+pageSize);
		List<SharedCard> sharedCards = jdbcTemplate.query(sql,new Object[] { cardid, pageSize }, new SharedCardMapper());
		CustomerContextHolder.clearCustomerType();
		return sharedCards;
	}
	
	@Override
	public List<SharedCard> getSharedCardThreeDay(Timestamp bt,Timestamp et) throws DataAccessException {
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_READ);
		String sql = "SELECT STATUS,Card_id,User_id,Store_id,Content,Share_type,Praise_count,Browse_count,Create_time FROM shared_card where share_type = 1 AND Create_time>? AND Create_time<?";
		List<SharedCard> sharedCards = jdbcTemplate.query(sql,new Object[] {bt,et}, new SharedCardMapper());
		CustomerContextHolder.clearCustomerType();
		return sharedCards;
	}

	@Override
	public int getUserSharedCardCount(Integer uid) {
		return 0;
	}*/
}
